﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace SQLServerDialog.DAL
{
    internal class BrowserDAL
    {
        private string conStr;        

        private const string fixedDrivesSP = "xp_fixeddrives";
        //private const string availableDrivesSP = "xp_availablemedia";
        private const string subFoldersSP = "xp_subdirs";
        private const string dirTreeSP = "xp_dirtree";
        private const string database = "Master";

        public BrowserDAL(string server, string user, string password)
        {
            conStr = string.Format("POOLING=FALSE;USER ID={0};PASSWORD={1};DATABASE={2};SERVER={3};",
                user, password, database, server);
        }

        public List<string> GetFixedDrives()
        {
            return Execute(fixedDrivesSP);
        }

        public List<string> GetSubFolders(string parentFolder)
        {
            return Execute(string.Format("{0} '{1}'", subFoldersSP, parentFolder));
        }

        internal List<string> GetFiles(string parentFolder,string[] ext)
        {
            StringBuilder sb = new StringBuilder();
            if (ext.Length > 0)
            {
                string queryFileExt = string.Join(" or ", ext.ToList().Where(fext => !string.IsNullOrEmpty(fext)).Select(fext => string.Format("RIGHT(subdirectory,{0}) = '{1}'", fext.Trim().Length, fext.Trim())).ToArray());
                if (!string.IsNullOrEmpty(queryFileExt))
                {
                    sb.Append(string.Format("AND({0})", queryFileExt));
                }
            }
            string command = string.Format("Declare @fileTable TABLE(id int IDENTITY(1,1),subdirectory nvarchar(512),depth int,isfile bit);INSERT @fileTable (subdirectory,depth,isfile)EXEC {0} '{1}',1,1;SELECT subdirectory FROM @fileTable WHERE isfile = 1 {2} ORDER BY id;",
                dirTreeSP, parentFolder, sb.ToString());
            return Execute(command);
        }

        private List<string> Execute(string cmdStr)
        {

            List<string> result = new List<string>();
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
                {
                    conn.Open();
                        try
                        {
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    result.Add(reader.GetString(0));
                                }
                            }
                        }
                        catch
                        {
                            //throw new Exception("Access Denied");
                        }

                }
            }
            return result;

        }   
    }
}
